/**
 * 描述: 业务逻辑处理 - 单词接口
 * 作者: wz
 * 日期: 2023-12-25
 */
const{deleteImageByURL ,uploadAvatar}  = require('../multer/upload')
const { querySql, formatTime, innerUploadFile } = require('../utils/index');
const boom = require('boom');
const { body, validationResult } = require('express-validator');
const {
    CODE_ERROR,
    CODE_SUCCESS,
} = require('../utils/constant');
const fs = require('fs')
const xlsx = require('node-xlsx');      // 读写xlsx的插件

//图片删除
async function deleteImg(req, res) {
    const { url } = req.body;
    if (!url) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少参数',
            data: null
        });
    }
    try {
     
     const deleteimgl =   await deleteImageByURL(url);
        res.json({ code: 0, msg: deleteimgl });
    } catch (err) {
        res.json({ code: -1, msg: err.message });
    }
}
//图片上传
 // 图片上传
 async function upload(req, res) {
   // 检查是否有文件被上传


 
   try {
   const  uploadRes = await uploadAvatar(req, res);
     res.send({
       meta: { code: 200, msg: '上传成功！' },
       data: { img_url: uploadRes }
     });
   } catch (error) {
     res.send({
       meta: { code: 500, msg: '上传失败！' },
       data: null
     });
   }
 }

// 获得所有的绘本信息
async function getDrawList(req, res, next) {
    let sql = `SELECT * FROM drawlist;`;
    try {
        let data = await querySql(sql);
        if (!data || data.length === 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '暂无数据',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '查询成功',
                data: data
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}

// 通过name信息在drawcontent数据库查找
async function getDrawContentByName(req, res, next) {
    let { name } = req.body;
    if (!name) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少name参数',
            data: null
        });
    }
    let sql = `SELECT * FROM drawcontent WHERE name = '${name}';`;

    try {
        let data = await querySql(sql);
        if (!data || data.length === 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '未找到相关数据',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '查询成功',
                data: data
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}

// 通过drawbook信息在drawlist数据库查找
async function getDrawContentByDrawbook(req, res, next) {
    let { drawbook } = req.body;

    if (!drawbook) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少drawbook参数',
            data: null
        });
    }
    let sql = `SELECT * FROM drawlist WHERE drawbook = '${drawbook}';`;

    try {
        let data = await querySql(sql);
        if (!data || data.length === 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '未找到相关数据',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '查询成功',
                data: data
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}// 删除内容// 删除内容
async function deleteDrawContent(req, res, next) {
    let { name, content, image } = req.body;
    console.log("111", req.body);
    
    // 参数校验
    if (!name || !content) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少name或content参数',
            data: null
        });
    }

    // 查询子数据库是否有相关数据
    let sqlSelect = `SELECT * FROM drawtail WHERE name = '${content}';`;
    try {
        let subData = await querySql(sqlSelect);
        
        // 如果子数据库有数据，先删除子数据库的数据
        if (subData && subData.length > 0) {
            // 删除子数据库中的记录
            let sqlDeleteSub = `DELETE FROM drawtail WHERE name = '${content}';`;
            try {
                let subResult = await querySql(sqlDeleteSub);
                console.log(`成功删除子数据库 ${subResult.affectedRows} 条数据`);
            } catch (err) {
                return res.json({
                    code: CODE_ERROR,
                    msg: '删除子数据库数据时出错',
                    data: err
                });
            }
        }

        // 删除父数据库中的记录
        let sqlDeleteParent = `DELETE FROM drawcontent WHERE name = '${name}' AND content = '${content}';`;
        try {
            let parentResult = await querySql(sqlDeleteParent);
            if (parentResult.affectedRows > 0) {
                res.json({
                    code: CODE_SUCCESS,
                    msg: `成功删除父数据库 ${parentResult.affectedRows} 条数据`,
                    data: null
                });
            } else {
                res.json({
                    code: CODE_SUCCESS,
                    msg: '未找到要删除的父数据库数据',
                    data: null
                });
            }
        } catch (err) {
            res.json({
                code: CODE_ERROR,
                msg: '删除父数据库数据时出错',
                data: err
            });
        }
    } catch (err) {
        return res.json({
            code: CODE_ERROR,
            msg: '查询子数据库时出错',
            data: err
        });
    }
}
// 创建新的绘本
async function addDraw(req, res) {
    try {
        // 处理图片上传
     
        const uploadResult = await uploadAvatar(req, res,1).catch(err => {
            console.error('uploadAvatar error:', err);
            throw err;
        });
      
        if (!uploadResult) {
            return res.json({
                code: CODE_ERROR,
                msg: '图片上传失败',
                data: null
            });
        }
        let {  imageUrl,
            otherFields } = uploadResult;
            let {  creator,
                drawbook } = otherFields;

        // 插入到drawlist表
        const insertDrawlistSql = `INSERT INTO drawlist (drawbook, creator, image, createtime, modifytime) VALUES ('${drawbook}', '${creator}', '${imageUrl}', NOW(), NOW())`;
         const params = [drawbook, creator, imageUrl];

        const drawlistResult = await querySql(insertDrawlistSql, params);
        if (drawlistResult.affectedRows > 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '创建成功',
                data: {
                    id: drawlistResult.insertId
                }
            });
        } else {
            res.json({
                code: CODE_ERROR,
                msg: '创建失败',
                data: null
            });
        }
    } catch (err) {
        let errorMsg = '图片上传或处理失败';
        if (err.message.includes('SQL')) {
            errorMsg = '数据库报错';
        }
        res.json({
            code: CODE_ERROR,
            msg: errorMsg,
            data: err
        });
    }
}

// 删除绘本
async function deleteDraw(req, res, next) {
    let { drawbook ,image,creator} = req.body;
	console.log(drawbook);
	console.log(image);
console.log(creator);
    if (!drawbook ||!creator) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少drawbook参数或creator参数',
            data: null
        });
    }

		if (image) {
		try {
		 
		 const deleteimgl =   await deleteImageByURL(image);
		 
		} catch (err) {

		}
		}
    // 删除drawcontent中的相关数据
    let deleteDrawcontentSql = `DELETE FROM drawcontent WHERE name = '${drawbook}' ;`;
    try {
        let drawcontentResult = await querySql(deleteDrawcontentSql);
        console.log('drawcontentResult', drawcontentResult);

        // 删除drawlist中的数据
        let deleteDrawlistSql = `DELETE FROM drawlist WHERE drawbook = '${drawbook}';`;
        let drawlistResult = await querySql(deleteDrawlistSql);
        console.log('drawlistResult', drawlistResult);

        if (drawlistResult.affectedRows > 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: `成功删除绘本及关联内容`,
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '未找到要删除的绘本',
                data: null
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}

// 添加绘本内容// 添加绘本内容
async function addContent(req, res) {
    try {
        // 处理图片上传
        const uploadResult = await uploadAvatar(req, res,2).catch(err => {
            console.error('uploadAvatar error:', err);
            throw err;
        });
      
        if (!uploadResult) {
            return res.json({
                code: CODE_ERROR,
                msg: '图片上传失败',
                data: null
            });
        }
        let { imageUrl, otherFields } = uploadResult;
        let { name, content } = otherFields;

        if (!name || !content) {
            return res.json({
                code: CODE_ERROR,
                msg: '缺少name或content参数',
                data: null
            });
        }

        // 插入到drawcontent表，包括image字段
        const insertDrawcontentSql = `INSERT INTO drawcontent (name, content, image) VALUES ('${name}', '${content}', '${imageUrl}');`;
        const params = [name, content, imageUrl];

        let result = await querySql(insertDrawcontentSql, params);
        if (result.affectedRows > 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '添加内容成功',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '添加内容失败',
                data: null
            });
        }
    } catch (err) {
        let errorMsg = '图片上传或处理失败';
        if (err.message.includes('SQL')) {
            errorMsg = '数据库报错';
        }
        res.json({
            code: CODE_ERROR,
            msg: errorMsg,
            data: err
        });
    }
}
// 新建：通过发过来的数据进行新建
async function createDrawtail(req, res) {
    try {
     
        // 处理图片上传
        let uploadResult2 = await uploadAvatar(req, res,2).catch(err => {
            console.error('uploadAvatar error:', err);
            throw err;
        });
      
        if (!uploadResult2) {
            return res.json({
                code: CODE_ERROR,
                msg: '图片上传失败',
                data: null
            });
        }
        let { imageUrl, otherFields } = uploadResult2;
        let { name, content } = otherFields;

        if (!name || !content) {
            return res.json({
                code: CODE_ERROR,
                msg: '缺少name或content参数',
                data: null
            });
        }
console.log(name);
console.log(content);
console.log(imageUrl);
        // 插入到drawcontent表，包括image字段
        let insertDrawtailSql = `INSERT INTO drawtail (name, image, content) VALUES ('${name}', '${imageUrl}', '${content}');`;
        

        let result = await querySql(insertDrawtailSql);
        if (result.affectedRows > 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '添加内容成功',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '添加内容失败',
                data: null
            });
        }
    } catch (err) {
        let errorMsg = '图片上传或处理失败';
        if (err.message.includes('SQL')) {
            errorMsg = '数据库报错';
        }
        res.json({
            code: CODE_ERROR,
            msg: errorMsg,
            data: err
        });
    }
}

// 删除：通过name和content关键词进行删除
async function deleteDrawtail(req, res, next) {
    let { name, content,image } = req.body;
    
    if (!name || !content) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少name或content参数',
            data: null
        });
    }
    if (image) {
    
     try {
      
      const deleteimgl =   await deleteImageByURL(image);
       
     } catch (err) {
       console.log("error  on picture")
     }
    }
    try {
     
     const deleteimgl =   await deleteImageByURL(url);
        res.json({ code: 0, msg: deleteimgl });
    } catch (err) {
        res.json({ code: -1, msg: err.message });
    }
    let sql = `DELETE FROM drawtail WHERE name = '${name}' AND content = '${content}';`;
    
    try {
        let result = await querySql(sql);
        if (result.affectedRows > 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: `成功删除 ${result.affectedRows} 条数据`,
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '未找到要删除的数据',
                data: null
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}

// 查所有：通过name查找所有的信息
async function getAllDrawtailByName(req, res, next) {
    let { name } = req.body;
    if (!name) {
        return res.json({
            code: CODE_ERROR,
            msg: '缺少name参数',
            data: null
        });
    }
    let sql = `SELECT * FROM drawtail WHERE name = '${name}';`;

    try {
        let data = await querySql(sql);
        if (!data || data.length === 0) {
            res.json({
                code: CODE_SUCCESS,
                msg: '未找到相关数据',
                data: null
            });
        } else {
            res.json({
                code: CODE_SUCCESS,
                msg: '查询成功',
                data: data
            });
        }
    } catch (err) {
        res.json({
            code: CODE_ERROR,
            msg: '数据库报错',
            data: err
        });
    }
}

module.exports = {
    deleteImg,
    getDrawContentByDrawbook,
    getDrawList,
    getDrawContentByName,
    deleteDrawContent,
    addDraw,
    deleteDraw,
    addContent,
    createDrawtail, // 新建方法
    deleteDrawtail, // 删除方法
	upload,
    getAllDrawtailByName // 查所有方法
};